package bookstore;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

/**
 * 注文データを表すBeanです
 */
public class Order implements java.io.Serializable {
    /**
	 *
	 */
	private static final long serialVersionUID = 1L;
	/** 代金引換 */
    public static int COLLECT = 1;
    /** 郵便振込 */
    public static int TRANSFER = 2;

    /** 支払い方法を定義した配列 */
    public static String[] PAYMENT_STRING = {"代金引換", "郵便振込"};
    /** お客様氏名ID */
    private int id;
    /** お客様氏名 */
    private String customerName;
    /** お届け先住所 */
    private String address;
    /** 支払い方法 */
    private int payment;
    /** 注文金額の合計 */
    private int sum;
    /** 注文時刻 */
    private Date orderDate;
    /** 注文アイテム */
    private OrderItem[] items;

    // getter/setterメソッド
    public String getAddress() {
        return address;
    }

    public String getCustomerName() {
        return customerName;
    }

    public int getId() {
        return id;
    }

    public Date getOrderDate() {
        return orderDate;
    }

    public int getPayment() {
        return payment;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public void setCustomerName(String customerName) {
        this.customerName = customerName;
    }

    public void setId(int id) {
        this.id = id;
    }

    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }

    public void setPayment(int payment) {
    	System.out.println("Payment->"+payment);
        this.payment = payment;
    }

    public OrderItem[] getItems() {
        return items;
    }

    public void setItems(OrderItem[] items) {
        this.items = items;
    }

    public String getPaymentString() {
    	System.out.println("PAYMENT->"+this.payment);
        return PAYMENT_STRING[payment - 1];
    }

    public int getSum() {
        if (sum != 0) {
            return sum;
        }
        for (int i = 0; i < this.items.length; i++) {
            sum = sum + this.items[i].getBook().getPrice()
                    * this.items[i].getNum();
        }
        return sum;
    }

    public void setSum(int sum) {
        this.sum = sum;
    }

    // 注文データ、注文アイテムをDBに登録
    public void store() throws SQLException {
        Connection conn = null;
        try {
            conn = new ResourceFinder().getConnection();
            conn.setAutoCommit(false);
            store_order(conn);
            store_order_item(conn);
            conn.commit();
        }catch(SQLException e){
            throw e;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {}
            }
        }
    }

    // 注文データをDBに登録
    private void store_order(Connection conn) throws SQLException {
        final String sql = "insert into Orders values(?, ?, ?, ?, current_timestamp)";
        PreparedStatement statement = conn.prepareStatement(sql);
        this.id = this.createNewId(conn);
        statement.setInt(1, this.id);
        statement.setString(2, this.customerName);
        statement.setString(3, this.address);
        statement.setInt(4, this.payment);
        statement.executeUpdate();
        statement.close();
    }

    // 新規IDを取得
    private int createNewId(Connection conn) throws SQLException {
        String sql = "UPDATE sequence SET id=LAST_INSERT_ID(id+1)";
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.executeUpdate();

        sql = "SELECT LAST_INSERT_ID()";
        statement = conn.prepareStatement(sql);
        ResultSet result = statement.executeQuery();
        int id = 0;
        if (result.next()) {
            id = result.getInt(1);
        }
        result.close();
        statement.close();
        return id;
    }

    // 注文アイテムをDBに登録
    private void store_order_item(Connection conn) throws SQLException {
        final String sql = "insert into Order_Item values(?, ?, ?, ?)";
        PreparedStatement statement = conn.prepareStatement(sql);
        for (int i = 0; i < this.items.length; i++) {
            statement.setInt(1, this.id);
            statement.setInt(2, i + 1);
            statement.setString(3, this.items[i].getBook().getId());
            statement.setInt(4, this.items[i].getNum());
            statement.addBatch();
        }
        statement.executeBatch();
        statement.close();
    }

    // DBから登録済みのデータを取得
    public static Order[] loadAllData() throws SQLException {
        Connection conn = null;
        List<Order> orderList = new ArrayList<Order>();
        try {
            conn = new ResourceFinder().getConnection();

            // order_idは昇順、item_idは降順でソート
            final String sql = "select o.id, o.customer_name, o.address, o.payment, o.order_date, "
                    + "i.item_id, i.book_id, b.title, b.author, b.publisher, b.price, i.num "
                    + "from orders o, order_item i, book b "
                    + "where o.id=i.order_id and i.book_id=b.id "
                    + "order by o.id, i.item_id desc";
            Statement statement = conn.createStatement();
            ResultSet result = statement.executeQuery(sql);
            List<OrderItem> orderItemList = new ArrayList<OrderItem>();
            while (result.next()) {
                Book book = new Book();
                book.setId(result.getString(7));
                book.setTitle(result.getString(8));
                book.setAuthor(result.getString(9));
                book.setPublisher(result.getString(10));
                book.setPrice(result.getInt(11));
                OrderItem item = new OrderItem();
                item.setBook(book);
                item.setNum(result.getInt(12));
                orderItemList.add(item);

                // item_idが1（最初の注文アイテム）のとき
                if (result.getInt(6) == 1) {
                    Order order = new Order();
                    order.setId(result.getInt(1));
                    order.setCustomerName(result.getString(2));
                    order.setAddress(result.getString(3));
                    order.setPayment(result.getInt(4));
                    order.setOrderDate(result.getTimestamp(5));
                    Collections.reverse(orderItemList);
                    order.setItems((OrderItem[]) orderItemList.toArray(new OrderItem[orderItemList.size()]));

                    orderList.add(order);
                    orderItemList.clear();
                }
            }
            result.close();
            statement.close();
        }catch(SQLException e){
            throw e;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }

        }
        return (Order[]) orderList.toArray(new Order[orderList.size()]);
    }

    // 集計結果が格納されたjava.util.Mapインスタンスを取得
    public static Map<Date,Integer> calculateMonthlyTotal(Order[] orders) {
        Map<Date,Integer> totalMap = new TreeMap<Date,Integer>();
        int sum = 0;
        for (int i = 0; i < orders.length; i++) {
            sum = sum + orders[i].getSum();
            Calendar tempCal = Calendar.getInstance();
            tempCal.setTime(orders[i].getOrderDate());
            Calendar calendar = Calendar.getInstance();
            Calendar nextCal = null;
            calendar.set(tempCal.get(Calendar.YEAR),
                         tempCal.get(Calendar.MONTH) + 1,
                         0,
                         0,
                         0,
                         0);
            if (i == orders.length - 1) {
                totalMap.put(calendar.getTime(), new Integer(sum));
                break;
            }
            nextCal = Calendar.getInstance();
            nextCal.setTime(orders[i + 1].getOrderDate());

            if (calendar.get(Calendar.YEAR) != nextCal.get(Calendar.YEAR)
                    || calendar.get(Calendar.MONTH) != nextCal.get(Calendar.MONTH)) {
                totalMap.put(calendar.getTime(), new Integer(sum));
                sum = 0;
            }
        }
        return totalMap;
    }
}